package com.teamTPZoo.DB;

import org.apache.log4j.Logger;

import  java.sql.*;
import  net.sf.json.*;

public final class TPZooDB {

    private static Logger     log               = Logger.getLogger(TPZooDB.class);

    public         Connection dbConnection      = null;
    public         Statement  dbStatement       = null;
    
    public         String     connectString     = "";
    public         String     connectUser       = "";
    public         String     connectPasswd     = "";
    
    private        Boolean    debug             = false;
    
    public TPZooDB( String connectDBMS, String connectHost, String connectUser, String connectPasswd, String connectPort, String connectDB, boolean autoConnect, boolean debug ) 
    {
        this.debug         = debug;
        this.connectString = this.getConnectionString(connectDBMS, connectHost, connectPort, connectDB);
        this.connectUser   = connectUser;
        this.connectPasswd = connectPasswd;
        
        
        try {
            Class.forName( this.getJDBCdriver(connectDBMS) );
            
            if ( autoConnect ) {
                this.dbConnect();
            }
        }
        catch ( ClassNotFoundException e ) {
            printDebugInfo( "e", "Error : ClassNotFoundException - " + e.getMessage() );
        }
    }
    

    public ResultSet  dbQueryRS( String strQuery )
    {
        ResultSet  dbResultSet   = null;
        try {
        	dbResultSet = this.dbStatement.executeQuery(strQuery);
        }
        catch ( SQLException e ) {
            printDebugInfo( "e", e.toString() );
        }
        
        return dbResultSet;
    }
    
    public JSONObject dbQueryJSON( String strQuery )
    {
        ResultSet  dbResultSet   = null;
        
        JSONObject returnSet     = new JSONObject();
        JSONObject returnDataSet = new JSONObject();
        JSONObject resultSet     = new JSONObject();
        int        cntLoop       = 0;
        
        try {
            dbResultSet = this.dbStatement.executeQuery(strQuery);
            
            while( dbResultSet.next() ) {
                resultSet.clear();
                for ( int cntLoop2=1; cntLoop2<=dbResultSet.getMetaData().getColumnCount(); cntLoop2++ ) {
                    resultSet.put( cntLoop2, dbResultSet.getString(cntLoop2) );
                }
                returnDataSet.put( cntLoop, resultSet );
                cntLoop++;
            }
            
            returnSet.put("rowCount", cntLoop );
            returnSet.put("colCount", dbResultSet.getMetaData().getColumnCount() );
            returnSet.put("data"    , returnDataSet);
        }
        catch ( SQLException e ) {
            returnSet.put("rowCount", cntLoop );
            returnSet.put("colCount", 0 );
            returnSet.put("data"    , "" );
        }
        
        if ( dbResultSet != null ) {
            try                  {    dbResultSet.close();     printDebugInfo( "d", "ResultSet Closed." );    } 
            catch( Exception e ) {                             printDebugInfo( "e", e.toString() );           }
        }
        
        return returnSet;
    }
    
    
    
    public int dbConnect( )
    {
        int returnValue = 0;
        
        try {
            this.dbConnection = DriverManager.getConnection( this.connectString, this.connectUser, this.connectPasswd );    printDebugInfo( "d", "Connect Success" );
            this.dbStatement  = this.dbConnection.createStatement();                                                        printDebugInfo( "d", "Statement Created." );
            returnValue = 1;
        }
        catch ( SQLException e ) {
            printDebugInfo( "e", "Connect Fail : " + e.toString() );
        }
        return returnValue;
    }
    
    public void dbClose( )
    {
        if ( this.dbStatement  != null ) {
            try { 
                this.dbStatement.close();     printDebugInfo( "d", "Statement Closed." );
            } 
            catch( Exception e ) {            printDebugInfo( "e", e.toString() );    }
        }
        if ( this.dbConnection != null ) {
            try { 
                this.dbConnection.close();    printDebugInfo( "d", "Connection Closed." );
            } 
            catch( Exception e ) {            printDebugInfo( "e", e.toString() );    }
        }
    }

    private String getJDBCdriver( String connectDBMS )
    {
        String returnValue = "";
        
        if      ( connectDBMS.equals("oracle") ) {    returnValue = "oracle.jdbc.driver.OracleDriver";                 }
        else if ( connectDBMS.equals("mssql")  ) {    returnValue = "com.microsoft.sqlserver.jdbc.SQLServerDriver";    }
        else if ( connectDBMS.equals("mysql")  ) {    returnValue = "com.mysql.jdbc.Driver";                           }
        
        return returnValue;
    }
    
    private String getConnectionString( String connectDBMS, String connectHost, String connectPort, String connectDB )
    {
        String returnValue = "";
        
        if      ( connectDBMS.equals("oracle") ) {    
            if ( "".equals(connectPort) )    {    connectPort = "1521";    }
            returnValue = "jdbc:oracle:thin:@"+connectHost+":"+connectPort+":"+connectDB;
        }
        else if ( connectDBMS.equals("mssql")  ) {    
            if ( "".equals(connectPort) )    {    connectPort = "1433";    }
            returnValue = "jdbc:sqlserver://"+connectHost+":"+connectPort+";DatabaseName="+connectDB;
        }
        else if ( connectDBMS.equals("mysql")  ) {    
            if ( "".equals(connectPort) )    {    connectPort = "3306";    }
            returnValue = "jdbc:mysql://"+connectHost+":"+connectPort+"/"+connectDB;
        }
        
        return returnValue;
    }
    
    
    
    private void printDebugInfo( String Mode, String strMsg )
    {
        if ( this.debug ) {
            if ( Mode.equals("e") ) {    log.error( strMsg );    }
            else                    {    log.debug(strMsg);      }
            //System.out.println( strMsg );
        }
    }

}
